﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using Excel = Microsoft.Office.Interop.Excel;
using ExcelLibrary;
using ExcelLibrary.SpreadSheet;
using cecbData;
using cecbClasses.Data_Adapters.Project;
using cecbData;
using cecbClasses;
using cecbClasses.Data_Classes;
using cecbClasses.Data_Adapters;
using System.Data;
using System.Collections.ObjectModel;

namespace CECB.Matrix
{
    /// <summary>
    /// Interaction logic for SimpleMatrixFinalResult_Window.xaml
    /// </summary>
    public partial class SimpleMatrixFinalResult_Window : Window
    {

        string destFile1;


        Func_Projects ObjProjctsFunc = new Func_Projects();

        Excel.Application oXL;
        Excel._Workbook oWB;
        Excel._Worksheet oSheet;
        String projectReference;
        public SimpleMatrixFinalResult_Window()
        {
            InitializeComponent();
            dataContext = new DSSEntities();
        }

        public SimpleMatrixFinalResult_Window(String projRef)
        {
            InitializeComponent();
            dataContext = new DSSEntities();
            projectReference = projRef;
            genarateExcel();

        }

        DSSEntities dataContext;


        private void genarateExcel()
        {

            String pref = projectReference;
           
            string startColName, endColName;
            int startColNo, endColNo;


            oXL = new Excel.Application();
            oXL.Visible = true;
             
            oWB = (Excel._Workbook)(oXL.Workbooks.Add());
            oSheet = (Excel._Worksheet)oWB.ActiveSheet;
         


            List<Get_Simple_Phase_Result> LstResult = new cecbClasses.Data_Adapters.MatrixSimple.SimpleMatrixCalculation().GetSimplePhaseValues(pref);
            List<Get_Simple_Activities_Result> lstActivities = new cecbClasses.Data_Adapters.MatrixSimple.SimpleMatrixCalculation().GetSimpleActivitiesValues(pref);
            List<Get_Simple_Impacts_Result> lstSimpleImpacts = new cecbClasses.Data_Adapters.MatrixSimple.SimpleMatrixCalculation().GetSimpleImpactsValues(pref);
            
            List<SimpleMatrixValues> lstsimValues = (from c in new DSSEntities().SimpleMatrixValues
                                                    where c.proj_reference.Equals(pref)
                                                    select c).ToList();
             
            oSheet.Cells[3, 1] = "Category";
            oSheet.get_Range("A3").ColumnWidth = 15;
            oSheet.get_Range("A3").Borders.Weight = 2;
           
            oSheet.Cells[3, 2] = "Component";
            oSheet.get_Range("B3").ColumnWidth = 25;
            oSheet.get_Range("B3").Borders.Weight = 2;
            oSheet.Cells[3, 4] = "Project Impact";
            oSheet.get_Range("D3").ColumnWidth = 70;
            oSheet.get_Range("C3", "D3").Merge();
            oSheet.get_Range("C3").Borders.Weight = 2;

            oSheet.get_Range("A3", "D3").Font.Bold = true;
            oSheet.get_Range("A3", "D3").VerticalAlignment = VerticalAlignment.Center;

            int z = 5;
            startColNo = 69;
            startColName = GetExcelColumnName(startColNo).ToString() + "2";

            //   Phases Headings 
            foreach (Get_Simple_Phase_Result oRsts in LstResult)
            {
                 
                endColNo = startColNo + (((int)oRsts.NoOfActivities)) - 1;

                endColName = GetExcelColumnName(endColNo).ToString() + "2";

                oSheet.Cells[2, z] = oRsts.actv_phase;

                oSheet.get_Range( GetExcelColumnName(z)+2).Font.Bold = true;

                oSheet.get_Range(startColName, endColName).Merge();
                oSheet.get_Range(startColName, endColName).HorizontalAlignment = HorizontalAlignment.Center;
                oSheet.get_Range(startColName, endColName).Borders.Weight = 2;
                oSheet.get_Range(startColName, endColName).Font.Bold = true;
                startColNo = endColNo + 1;
                startColName = GetExcelColumnName(startColNo).ToString() + "2";

                z = z + (int)oRsts.NoOfActivities;

            }

            // Get Activities  
            startColNo = 69;
            startColName = GetExcelColumnName(startColNo).ToString() + "4";

            int startColNo2 = 69;
            string startColName2 = GetExcelColumnName(startColNo2).ToString() + "3";
            int endColNo2;
            string endColName2;

            int ActNo = 1;
            z = 5;
            foreach (Get_Simple_Activities_Result oRstAct in lstActivities)
            {
                endColNo2 = startColNo2 + 1;
                endColName2 = GetExcelColumnName(endColNo2).ToString() + "3";

                endColNo = startColNo + 1;
                endColName = GetExcelColumnName(endColNo).ToString() + "4";

                oSheet.Cells[3, z] = ActNo;
                oSheet.Cells[4, z] = oRstAct.actv_name;

                oSheet.get_Range(GetExcelColumnName(z) + 4).Font.Bold = true;
                oSheet.get_Range(GetExcelColumnName(z) + 3).Font.Bold = true;
                oSheet.get_Range(GetExcelColumnName(z)+4).Orientation = 90;
                oSheet.get_Range(GetExcelColumnName(z) + 4).RowHeight = 200;
                oSheet.get_Range(GetExcelColumnName(z) + 4).WrapText = true;
                 
                oSheet.get_Range(GetExcelColumnName(z) + 4).Borders.Weight = 2;
                 
                oSheet.get_Range(startColName2, endColName2).HorizontalAlignment = HorizontalAlignment.Center;
                oSheet.get_Range(startColName2, endColName2).Borders.Weight = 2;


                startColNo = startColNo + 2;
                startColName = GetExcelColumnName(startColNo).ToString() + "4";

                startColNo2 = startColNo2 + 2;
                startColName2 = GetExcelColumnName(startColNo2).ToString() + "3";
                z = z + 1;

                ActNo = ActNo + 1;
            }

             
            //  IMPACTS Print Impacts , Magnitude, Imports
            startColNo = 69;
            startColName = GetExcelColumnName(startColNo).ToString() + "5";

            string   srtImpColNoName, endImpColNoName, srtImpColName, endImpColName;

            int srtCatNo,   srtSubNo;
            int LineNO = 1;
            int rowNo = 5;

            float GrandTotal = 0;

            string CatName = lstSimpleImpacts[0].cmpt_mainCat.ToString();
            oSheet.Cells[5, 1] = CatName;

            srtCatNo = 5;
            srtSubNo = 5;
            string SubName = lstSimpleImpacts[0].cmpt_name.ToString();
            oSheet.Cells[5, 2] = SubName;

            foreach (Get_Simple_Impacts_Result oRstImpacts in lstSimpleImpacts)
            {
                z = 5;

                if (CatName != oRstImpacts.cmpt_mainCat)
                {
                    srtCatNo = rowNo;
                    CatName = oRstImpacts.cmpt_mainCat;
                    oSheet.Cells[rowNo, 1] = CatName;
                }

                if (SubName != oRstImpacts.cmpt_name)
                {
                    srtSubNo = rowNo;
                    SubName = oRstImpacts.cmpt_name;
                    oSheet.Cells[rowNo, 2] = SubName;
                    oSheet.get_Range(GetExcelColumnName(rowNo) + 4).Borders.Weight = 2;
                }

                startColNo = 69;
                startColName = GetExcelColumnName(startColNo).ToString() + rowNo;

                endColNo = startColNo + 1;
                endColName = GetExcelColumnName(endColNo).ToString() + rowNo;

                oSheet.Cells[rowNo, 4] = oRstImpacts.impt_name;

                oSheet.Cells[rowNo, 3] = LineNO;
                srtImpColNoName = "C" + rowNo;
                endImpColNoName = "C" + (rowNo + 1);

                srtImpColName = "D" + rowNo;
                endImpColName = "D" + (rowNo + 1);
                 
                float Total = 0;
                
                //   MATRIX DATA 
                foreach (Get_Simple_Activities_Result oRstAct in lstActivities)
                {
                    endColNo = startColNo + 1;
                    endColName = GetExcelColumnName(endColNo).ToString() + rowNo;
                   
                    var filter = (from i in lstsimValues
                                  where i.actv_reference == oRstAct.actv_reference && i.impt_reference == oRstImpacts.impt_reference
                                  select i).ToList();

                    if (filter.Count() > 0)
                    {
                         float svalue = 0;

                        try
                        {
                             svalue = (float)filter[0].mtrx_value;
                        }
                        catch (InvalidOperationException)
                        {
                            svalue = 0;
                        }
                        oSheet.Cells[rowNo, z] = svalue;
                        oSheet.get_Range(GetExcelColumnName(z) + rowNo).Borders.Weight = 2;
                        oSheet.get_Range(GetExcelColumnName(z) + rowNo).ColumnWidth = 2.5;
                        Total = Total + svalue;


                         
                    }

                    oSheet.get_Range(startColName, endColName).ColumnWidth = 2.5;
                     
                    startColNo = endColNo + 1;
                    startColName = GetExcelColumnName(startColNo).ToString() + rowNo;

                    z = z + 1;
                }

                oSheet.Cells[rowNo, z] = Total;
                oSheet.get_Range(GetExcelColumnName(z) + rowNo).Borders.Weight = 3;
                 
                endColName = GetExcelColumnName(startColNo) + (rowNo + 1).ToString();
                oSheet.get_Range(startColName, endColName).Merge();


                GrandTotal = GrandTotal + Total;

                rowNo = rowNo + 2;
                LineNO = LineNO + 1;
            }

            
          
            cecbClasses.Data_Adapters.MatrixSimple.SimpleMatrixCalculation SM = new cecbClasses.Data_Adapters.MatrixSimple.SimpleMatrixCalculation();
            bool isCorrectPrecentage1;
            double valPrecentage1;
            SM.getSociologicallyAffectionPresentage(pref, out isCorrectPrecentage1, out valPrecentage1);
             

            bool isCorrectPrecentage2;
            double valPrecentage2;
            SM.getEchologicallyAffectinPresentage(pref, out isCorrectPrecentage2, out valPrecentage2);
             

            bool isCorrectPrecentage3;
            double valPrecentage3;
            SM.getPhysicalEnvironmentalAffectionPresentage(pref, out isCorrectPrecentage3, out valPrecentage3);

            oSheet.Cells[4, z] = "Total";
            oSheet.get_Range(GetExcelColumnName(z) + 4).Borders.Weight = 2;
            oSheet.Cells[rowNo, z] = GrandTotal;
            oSheet.get_Range(GetExcelColumnName(z) + rowNo).Borders.Weight = 2;
            oSheet.Cells[rowNo + 1, z ] = "Physical Impact";
            oSheet.get_Range(GetExcelColumnName(z) + rowNo+1).Borders.Weight = 2;
            oSheet.Cells[rowNo + 2, z ] = "Sociological Impact";
            oSheet.get_Range(GetExcelColumnName(z) + rowNo+2).Borders.Weight = 2;
            oSheet.Cells[rowNo + 3, z ] = "Echological Impact";
            oSheet.get_Range(GetExcelColumnName(z) + rowNo+3).Borders.Weight = 2;
            oSheet.Cells[rowNo + 1, z+1] = valPrecentage3.ToString()+"%";
            oSheet.get_Range(GetExcelColumnName(z+1) + rowNo+1).Borders.Weight = 2;
            oSheet.Cells[rowNo + 2, z+1] = valPrecentage1.ToString()+"%";
            oSheet.get_Range(GetExcelColumnName(z+1) + rowNo+2).Borders.Weight = 2;
            oSheet.Cells[rowNo + 3, z + 1] = valPrecentage2.ToString() + "%";
            oSheet.get_Range(GetExcelColumnName(z+1) + rowNo+3).Borders.Weight = 2;
 



            //  Calculate Weighted Avg 
            decimal WAGrandTotal = 0;
             
            int catStrLNo = 5;

            oSheet.Cells[4, z + 1] = "Weighted Average";
            oSheet.get_Range(GetExcelColumnName(z+1) + 4).Borders.Weight = 2;
            oSheet.get_Range(GetExcelColumnName(z + 1) + rowNo).ColumnWidth = 5;
            for (int Lno = 5; Lno <= rowNo - 1; Lno = Lno+2)
            {

                var cell = (Excel.Range)oSheet.Cells[Lno, z];

                if (Convert.ToString(cell.Value2) != null)
                {  try
                    {
                         WAGrandTotal = WAGrandTotal +  (decimal)cell.Value2 / (int)GrandTotal;
                         oSheet.Cells[catStrLNo, z + 1] = (decimal)cell.Value2 / (int)GrandTotal;
                         oSheet.get_Range(GetExcelColumnName(z + 1) + catStrLNo).Borders.Weight = 3;
                    }
                    catch (System.DivideByZeroException)
                    {

                        WAGrandTotal = WAGrandTotal + (decimal)cell.Value2 / 1;
                        oSheet.Cells[catStrLNo, z + 1] = (decimal)cell.Value2 / 1;
                        oSheet.get_Range(GetExcelColumnName(z + 1) + catStrLNo).Borders.Weight = 3;
                    }

                     
                        catStrLNo = Lno + 2;
                      
                }
            }
            oSheet.Cells[catStrLNo, z + 1] = WAGrandTotal;
            oSheet.get_Range(GetExcelColumnName(z + 1) + catStrLNo).Borders.Weight = 3;


            oXL.Visible = true;
            oXL.UserControl = true;

        }

        private string GetExcelColumnName(int columnNumber)
        {
       
            if (columnNumber < 1)
                columnNumber = 0;

            int dividend = columnNumber;
            string columnName = "";
            int modulo;
            
            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }

         
        private void DrawCellBorders(int frmrowNo, int frmColNo, int TorowNo, int ToColNo, bool top, bool bottom, bool left, bool right, bool diagup)
        {
            Excel.Borders b = null;
            Excel.Borders fb = null;

            b = (Excel.Borders)(oSheet.Cells[frmrowNo, frmColNo] as Excel.Range).Borders;
            fb = (oSheet.Cells[frmrowNo, frmColNo] as Excel.Range).Borders;


            if (diagup == true)
            {
                fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].Weight;
            }
            if (top == true)
            {
                fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight;
            }
            if (bottom == true)
            {
                fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight;
            }
            if (left == true)
            {
                fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight;
            }

            if (right == true)
            {
                fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight;
            }




        }
         

        private void button_Closefrm_Click(object sender, RoutedEventArgs e)
        {
            this.Close();
        }

  


     

        private void Image_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
        {
            this.Close();
        }

        private void NewButton_Click(object sender, RoutedEventArgs e)
        {

        }

        private void button_insertEIADetails_Click(object sender, RoutedEventArgs e)
        {

        }

        private void button_ClearAll_Click(object sender, RoutedEventArgs e)
        {

        }

        private void button_save_Click(object sender, RoutedEventArgs e)
        {

        }

        
    }
}
